package dbops

import (
	"api/defs"
	"api/utils"
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"time"
)


func AddUserCredential(loginName string, pwd string) error {
	//db := openConn() 如果写在这里会重复执行 不合理 需要复用
	stmtIns, err := dbConn.Prepare("INSERT INTO users (login_name, pwd) VALUES(?,?)")//Prepare预编译提高安全性 防止撞库攻击?
	// 预处理是 MySQL 为了防止客户端频繁请求的一种技术，是对相同处理语句进行预先加载在 MySQL
	//Prepare 执行预处理 SQL 语句，并返回 Stmt 结构体指针，进行数据绑定操作。
	if err != nil{
		return err
	}
	_, err = stmtIns.Exec(loginName,pwd)
	if err != nil{
		return err
	}

	defer stmtIns.Close()
	//defer stmtIns.Close()延迟调用在性能上是有损耗的
	return nil
}

func GetUserCredential(loginName string) (string, error) {
	//db := openConn()
	stmtOut, err := dbConn.Prepare("SELECT pwd FROM users WHERE login_name = ?")
	if err != nil{
		log.Printf("%s",err)
		return "", err
	}

	var pwd string
	err = stmtOut.QueryRow(loginName).Scan(&pwd)
	if err != nil && err != sql.ErrNoRows{
		return "", err //处理返回是空的Rows的信息
	}

	defer stmtOut.Close()

	return pwd, nil
}

func DeleteUser(loginName string, pwd string) error {
	stmtDel, err := dbConn.Prepare("DELETE FROM users WHERE login_name = ? AND pwd = ?")
	if err != nil{
		log.Printf("删除用户失败:%s",err)
		return err
	}

	_, err = stmtDel.Exec(loginName,pwd)
	if err != nil{
		return err
	}
	defer stmtDel.Close()
	return nil
}

/*//为了接口标准化 函数返回defs.VideoInfo整个对象而不是调用对象的其中一个属性
func AddNewVideo(aid int, name string) (*defs.VideoInfo, error){
	//create uuid
	vid, err := utils.NewUUID()
	if err != nil{
		return nil, err
	}

	t := time.Now()
	ctime := t.Format("Jan 02 2006, 15:04:05")
	//库自带的时间原点 固定时间格式不要改哈 layout改了就坏事！
	stmtIns, err := dbConn.Prepare(`INSERT INTO video_info
					(id, author_id, name, display_ctime) VALUES(?,?,?,?)`)
	if err != nil{
		return nil, err
	}

	_, err = stmtIns.Exec(vid,aid,name,ctime)
	if err != nil{
		return nil, err
	}

	res := &defs.VideoInfo{Id: vid, AuthorId: aid, Name: name, DisplayCtime: ctime}

	defer stmtIns.Close()

	return res, nil
}*/


func AddNewVideo(aid int, name string) (*defs.VideoInfo, error) {
	// create uuid
	vid, err := utils.NewUUID()
	if err != nil {
		return nil, err
	}

	t := time.Now()
	ctime := t.Format("Jan 02 2006, 15:04:05")
	stmtIns, err := dbConn.Prepare(`INSERT INTO video_info 
		(id, author_id, name, display_ctime) VALUES(?, ?, ?, ?)`)
	if err != nil {
		return nil, err
	}

	_, err = stmtIns.Exec(vid, aid, name, ctime)
	if err != nil {
		return nil, err
	}

	res := &defs.VideoInfo{Id: vid, AuthorId: aid, Name: name, DisplayCtime: ctime}

	defer stmtIns.Close()
	return res, nil
}


//直接挪的轮子 记得好好研究看明白

func GetVideoInfo(vid string) (*defs.VideoInfo, error) {
	stmtOut, err := dbConn.Prepare("SELECT author_id, name, display_ctime FROM video_info WHERE id=?")

	var aid int
	var dct string
	var name string

	err = stmtOut.QueryRow(vid).Scan(&aid, &name, &dct)
	if err != nil && err != sql.ErrNoRows{
		return nil, err
	}

	if err == sql.ErrNoRows {
		return nil, nil
	}

	defer stmtOut.Close()

	res := &defs.VideoInfo{Id: vid, AuthorId: aid, Name: name, DisplayCtime: dct}

	return res, nil
}


func ListVideoInfo(uname string, from, to int) ([]*defs.VideoInfo, error) {
	stmtOut, err := dbConn.Prepare(`SELECT video_info.id, video_info.author_id, video_info.name, video_info.display_ctime FROM video_info 
		INNER JOIN users ON video_info.author_id = users.id
		WHERE users.login_name = ? AND video_info.create_time > FROM_UNIXTIME(?) AND video_info.create_time <= FROM_UNIXTIME(?) 
		ORDER BY video_info.create_time DESC`)


	var res []*defs.VideoInfo

	if err != nil {
		return res, err
	}

	rows, err := stmtOut.Query(uname, from, to)
	if err != nil {
		log.Printf("%s", err)
		return res, err
	}

	for rows.Next() {
		var id, name, ctime string
		var aid int
		if err := rows.Scan(&id, &aid, &name, &ctime); err != nil {
			return res, err
		}

		vi := &defs.VideoInfo{Id: id, AuthorId: aid, Name: name, DisplayCtime: ctime}
		res = append(res, vi)
	}

	defer stmtOut.Close()

	return res, nil
}

func DeleteVideoInfo(vid string) error {
	stmtDel, err := dbConn.Prepare("DELETE FROM video_info WHERE id=?")
	if err != nil {
		return err
	}

	_, err = stmtDel.Exec(vid)
	if err != nil {
		return err
	}

	defer stmtDel.Close()
	return nil
}

//直接挪的轮子 记得好好研究看明白

func AddNewComments(vid string, aid int, content string) error {
	id, err := utils.NewUUID()
	if err != nil{
		return err
	}

	stmtIns, err := dbConn.Prepare("INSERT INTO comments (id, video_id, author_id, content) values (? , ?, ? ,?)")
	if err != nil{
		return err
	}

	_, err = stmtIns.Exec(id, vid ,aid ,content)
	if err != nil{
		return err
	}

	defer stmtIns.Close()
	return nil
}

func  ListComments(vid string, from, to int) ([]*defs.Comment, error) {
	stmtOut, err := dbConn.Prepare(` SELECT comments.id, users.Login_name, comments.content FROM comments
 		INNER JOIN users ON comments.author_id = users.id
 		WHERE comments.video_id = ? AND comments.time > FROM_UNIXTIME(?) AND comments.time <= FROM_UNIXTIME(?)
 	`)

	var res []*defs.Comment

	rows, err := stmtOut.Query(vid, from, to)
	if err != nil{
		return res, err
	}

	for rows.Next(){
		var id, name, content string
		if err := rows.Scan(&id, &name, &content); err != nil{
			return res, err
		}

		c:= &defs.Comment{Id:id, VideoId: vid, AuthorId:name, Content: content}
		res = append(res, c)

	}

	defer stmtOut.Close()
	return res, nil
}